package com.pub.dbo.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import org.apache.log4j.Logger;

import com.pub.db.DBConnectionManager;
import com.pub.dbo.dao.ProxyDAO;
import com.pub.dbo.domain.Proxy;
import com.pub.util.StringUtils;
import com.pub.util.SystemUtils;


public class ProxyImpl implements ProxyDAO
{
	private static Logger logger;    
	
	private String DBTag;
	
	private DBConnectionManager dbcm;
	
	static
	{
		logger = Logger.getLogger (ProxyImpl.class.getName());
	}
	
	public ProxyImpl(String DBTag)
	{
		this.DBTag 	= DBTag;
		dbcm 		= DBConnectionManager.getInstance(ProxyImpl.class.getName());
	}

	public ProxyImpl(String DBTag, String configurePath)
	{
		this.DBTag 	= DBTag;
		dbcm 		= DBConnectionManager.getInstance(ProxyImpl.class.getName(), configurePath);
	}
	
	public int insert(Proxy proxy) 
	{
		int id = 0;
		
		StringBuilder sbSql = new StringBuilder(200);
		
		Connection conn = null;
		Statement sm 	= null;
		ResultSet rs 	= null;
		
		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			sbSql.append("INSERT INTO proxy SET isProcessed = `NO`, status = `N`, createdTime = UTC_TIMESTAMP(), modifiedTime = UTC_TIMESTAMP(), ip = `");
			sbSql.append(proxy.getIP());
			
			sbSql.append("`, port = `");
			sbSql.append(proxy.getPort());
			
			sbSql.append("`, country = `");
			sbSql.append(proxy.getCountry());
			
			sbSql.append("`,  username= `");
			sbSql.append(StringUtils.addcslashes(proxy.getUserName()));
			
			sbSql.append("`, password = `");
			sbSql.append(StringUtils.addcslashes(proxy.getPassword()));
			
			sbSql.append("`, rating = `");
			sbSql.append(proxy.getRating());
			
			sbSql.append("`, responseTime = `");
			sbSql.append(proxy.getResponseTime());
			
			sbSql.append("`, speed = `");
			sbSql.append(proxy.getSpeed());
			
			sbSql.append("`, summary=`");
			sbSql.append(StringUtils.addcslashes(proxy.getSummary()));
			
			sbSql.append("`, anonymous=`");
			sbSql.append(StringUtils.addcslashes(proxy.getAnonymous()));
				
			sbSql.append("` ON DUPLICATE KEY UPDATE country=VALUES(country), ip=VALUES(ip), port=VALUES(port), username=VALUES(username), password=VALUES(password)");
			sbSql.append(", rating=VALUES(rating), responseTime=VALUES(responseTime), speed=VALUES(speed), summary=VALUES(summary), anonymous=VALUES(anonymous)");
			sbSql.append(", isProcessed=VALUES(isProcessed), status=VALUES(status), createdTime=VALUES(createdTime), modifiedTime=VALUES(modifiedTime)");

			sm.executeUpdate(sbSql.toString(), Statement.RETURN_GENERATED_KEYS);
			rs = sm.getGeneratedKeys();
			if ( rs.next() ) {
				id = rs.getInt(1);
			}
		}catch(Exception e){
			logger.error("error SQL : " + sbSql.toString() + ", Exception : " + e.getMessage());
		}finally{
			if(id == 0 && conn != null){
				try {
					conn.rollback();
				} catch (SQLException e) {
				}
			}
			
			try{
				if(rs != null)
					rs.close();
			}catch(SQLException e){}
			
			try{
				if(sm != null)
					sm.close();
			}catch(SQLException e){}
			
			if(conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		return id;
	}

	
	public int update(Proxy proxy) 
	{
		int updateCount = 0;
		
		StringBuilder sbSql = new StringBuilder(200);
		
		Connection conn = null;
		Statement sm 	= null;
		ResultSet rs 	= null;
		
		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			sbSql.append("INSERT INTO proxy SET createdTime = UTC_TIMESTAMP(), modifiedTime = UTC_TIMESTAMP(), id = ");
			sbSql.append(proxy.getID());
			
			sbSql.append(", ip = '");
			sbSql.append(proxy.getIP());
			
			sbSql.append("', port = ");
			sbSql.append(proxy.getPort());
			
			sbSql.append(", country = '");
			sbSql.append(proxy.getCountry());
			
			sbSql.append("',  username= '");
			sbSql.append(StringUtils.addcslashes(proxy.getUserName()));
			
			sbSql.append("', password = '");
			sbSql.append(StringUtils.addcslashes(proxy.getPassword()));
			
			sbSql.append("', rating = '");
			sbSql.append(proxy.getRating());
			
			sbSql.append("', responseTime = '");
			sbSql.append(proxy.getResponseTime());
			
			sbSql.append("', speed = '");
			sbSql.append(proxy.getSpeed());
			
			sbSql.append("', summary='");
			sbSql.append(StringUtils.addcslashes(proxy.getSummary()));
			
			sbSql.append("', anonymous='");
			sbSql.append(StringUtils.addcslashes(proxy.getAnonymous()));
				
			sbSql.append("', isProcessed = '");
			sbSql.append(proxy.getIsProcessed());
			
			sbSql.append("', status = '");
			sbSql.append(proxy.getStatus());
		
			sbSql.append("' ON DUPLICATE KEY UPDATE country=VALUES(country), ip=VALUES(ip), port=VALUES(port), username=VALUES(username), password=VALUES(password)");
			sbSql.append(", rating=VALUES(rating), responseTime=VALUES(responseTime), speed=VALUES(speed), summary=VALUES(summary), anonymous=VALUES(anonymous)");
			sbSql.append(", isProcessed=VALUES(isProcessed), status=VALUES(status), modifiedTime=VALUES(modifiedTime)");

			updateCount = sm.executeUpdate(sbSql.toString());
		}catch(Exception e){
			logger.error("table object insert operation error.");
			e.printStackTrace();
		}finally{
			if(updateCount == 0 && conn != null){
				try {
					conn.rollback();
				} catch (SQLException e) {
					logger.error("object rollback to insert operation error.");
					e.printStackTrace();
				}
			}
			
			try{
				if(rs != null)
					rs.close();
			}catch(SQLException e){}
			
			try{
				if(sm != null)
					sm.close();
			}catch(SQLException e){}
			
			if(conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		return updateCount;
	}
	
	@Override
	public Vector<Proxy> getWaitAuthList()
	{
		Connection conn = null;
		Statement sm 	= null;
		ResultSet rs 	= null;

		Vector<Proxy> vtReturn = new Vector<Proxy>(82);
		
		StringBuilder sbSql = new StringBuilder(200);
		
		try {
			conn 	= dbcm.getConnection(DBTag);
			sm		= conn.createStatement();
	          
			sbSql.append("SELECT id, ip, port, country, username, password, rating, responseTime, speed, anonymous, summary, status, createdTime, modifiedTime FROM proxy WHERE isProcessed = 'false' OR status = 'Y'");
			
			Proxy proxy;
			rs 	= sm.executeQuery(sbSql.toString());
			
			while(rs.next()){
				proxy = new Proxy();
				
				proxy.setIsProcessed(false);
				
				proxy.setID(rs.getInt(1));
				proxy.setIP(rs.getString(2));
				proxy.setPort(rs.getInt(3));
				proxy.setCountry(rs.getString(4));
				
				proxy.setUserName(rs.getString(5));
				proxy.setPassword(rs.getString(6));
				proxy.setRating(rs.getInt(7));
				
				proxy.setResponseTime(rs.getInt(8));
				proxy.setSpeed(rs.getInt(9));
				
				proxy.setAnonymous(rs.getString(10));
				proxy.setSummary(rs.getString(11));
				proxy.setStatus(rs.getString(12));
				
				proxy.setCreatedTime(rs.getString(13));
				proxy.setModifiedTime(rs.getString(14));
				
				vtReturn.add(proxy);
			}
		} catch (Exception e) {
			logger.error("error SQL : " + sbSql.toString() + ", Exception : " + e.getMessage());
		}finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if(sm != null){
				try {
					sm.close();
				} catch (SQLException e) {
				}
			}
			
			if(conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		return vtReturn;
	}

	@Override
	public Vector<Proxy> getList()
	{
		Connection conn = null;
		Statement sm 	= null;
		ResultSet rs 	= null;

		Vector<Proxy> vtReturn = new Vector<Proxy>(82);
		
		StringBuilder sbSql = new StringBuilder(200);
		
		try {
			conn 	= dbcm.getConnection(DBTag);
			sm		= conn.createStatement();
	          
			sbSql.append("SELECT id, ip, port, country, username, password, rating, responseTime, speed, anonymous, summary, status, createdTime, modifiedTime FROM proxy WHERE status = 'Y' LIMIT 1000");
			
			Proxy proxy;
			rs 	= sm.executeQuery(sbSql.toString());
			
			while(rs.next()){
				proxy = new Proxy();
				
				proxy.setIsProcessed(false);
				
				proxy.setID(rs.getInt(1));
				proxy.setIP(rs.getString(2));
				proxy.setPort(rs.getInt(3));
				proxy.setCountry(rs.getString(4));
				
				proxy.setUserName(rs.getString(5));
				proxy.setPassword(rs.getString(6));
				proxy.setRating(rs.getInt(7));
				
				proxy.setResponseTime(rs.getInt(8));
				proxy.setSpeed(rs.getInt(9));
				
				proxy.setAnonymous(rs.getString(10));
				proxy.setSummary(rs.getString(11));
				proxy.setStatus(rs.getString(12));
				
				proxy.setCreatedTime(rs.getString(13));
				proxy.setModifiedTime(rs.getString(14));
				
				vtReturn.add(proxy);
			}
		} catch (Exception e) {
			logger.error("error SQL : " + sbSql.toString() + ", Exception : " + e.getMessage());
		}finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if(sm != null){
				try {
					sm.close();
				} catch (SQLException e) {
				}
			}
			
			if(conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		return vtReturn;
	}
	
	@Override
	public Vector<Proxy> getList(int limit)
	{
		Connection conn = null;
		Statement sm 	= null;
		ResultSet rs 	= null;

		Vector<Proxy> vtReturn = new Vector<Proxy>(82);
		
		StringBuilder sbSql = new StringBuilder(200);
		
		try {
			conn 	= dbcm.getConnection(DBTag);
			sm		= conn.createStatement();
	          
			sbSql.append("SELECT id, ip, port, country, username, password, rating, responseTime, speed, anonymous, summary, status, createdTime, modifiedTime FROM proxy WHERE status = 'Y' LIMIT ");
			sbSql.append(limit);
			
			Proxy proxy;
			rs 	= sm.executeQuery(sbSql.toString());
			
			while(rs.next()){
				proxy = new Proxy();
				
				proxy.setIsProcessed(false);
				
				proxy.setID(rs.getInt(1));
				proxy.setIP(rs.getString(2));
				proxy.setPort(rs.getInt(3));
				proxy.setCountry(rs.getString(4));
				
				proxy.setUserName(rs.getString(5));
				proxy.setPassword(rs.getString(6));
				proxy.setRating(rs.getInt(7));
				
				proxy.setResponseTime(rs.getInt(8));
				proxy.setSpeed(rs.getInt(9));
				
				proxy.setAnonymous(rs.getString(10));
				proxy.setSummary(rs.getString(11));
				proxy.setStatus(rs.getString(12));
				
				proxy.setCreatedTime(rs.getString(13));
				proxy.setModifiedTime(rs.getString(14));
				
				vtReturn.add(proxy);
			}
		} catch (Exception e) {
			logger.error("error SQL : " + sbSql.toString() + ", Exception : " + SystemUtils.getStackTrace(e));
		}finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if(sm != null){
				try {
					sm.close();
				} catch (SQLException e) {
				}
			}
			
			if(conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		return vtReturn;
	}
	
	public void close() 
	{
		dbcm.release(ProxyImpl.class.getName());
	}


}
